﻿using NCP.Core.DataUtil;
using NCP.Core.Exceptions;
using NCP.Core.Extensions;
using NCP.Core.IoC;
using NCP.Model;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;

namespace NCP.Service
{
    public class GroupChoseDrugCompareService : IService
    {
        private SqlSugarClient _client;
        public GroupChoseDrugCompareService()
        {
            _client = DbHelper.GetSqlSugarClient();
        }
        public int Compare(string gpCode, string ChosedrugCode, List<GroupChoseDrugCompare> comparelist)
        {
            _client.BeginTran();
            try
            {
                _client.Deleteable<GroupChoseDrugCompare>().Where(t => t.GpCode == gpCode && t.ChosedrugCode == ChosedrugCode).ExecuteCommand();
                _client.Insertable<GroupChoseDrugCompare>(comparelist).ExecuteCommand();
                _client.CommitTran();
            }
            catch (Exception e)
            {
                _client.RollbackTran();
                return 0;
            }
            return 1;
        }
        //获取中选药品对应的非中选药品
        public List<GroupChoseDrugCompareNew> GetUnChosedrugCompares(string gpCode, string drugCode)
        {
            var result = _client.SqlQueryable<GroupChoseDrugCompareNew>(@" select e.GpCode,  e.UnChosedrugCode ,o.DrugName UnChosedrugName,e.Ratio,o.Specs,o.UnitPrice,o.SpellCode,o.ProducerName
  from GroupChoseDrugCompare  e
  left join PhaBaseInfo o on e.UnChosedrugCode=o.DrugCode
 where e.GpCode=@gpCode and e.ChosedrugCode=@drugCode 
").AddParameters(new { gpCode = gpCode, drugCode = drugCode, })
.ToList();
            return result;

        }

        //获取中选药品对应的同品种
        public List<GroupVarietyDrugCompareNew> GetVarietydrugCompares(string gpCode, string drugCode)
        {
            var result = _client.SqlQueryable<GroupVarietyDrugCompareNew>(@" select e.GpCode,  e.VarietyDrugCode ,o.DrugName VarietyDrugName,e.Ratio,o.Specs,o.UnitPrice,o.SpellCode,o.ProducerName
  from GroupVarietyDrugCompare  e
  left join PhaBaseInfo o on e.VarietyDrugCode=o.DrugCode
 where e.GpCode=@gpCode and e.ChosedrugCode=@drugCode 
").AddParameters(new { gpCode = gpCode, drugCode = drugCode, })
.ToList();
            return result;

        }
        public List<GroupPurchase> GroupPurchaseSelect()
        {
            var result = _client.Queryable<GroupPurchase>().OrderBy("Year").ToList();
            return result;
        }
        //根据筛选获取批次
        public List<GroupPurchase> GroupPurchaseSelectByParam(string param)
        {
            string sql = "";

            if (param == "0")
            {
                var result = _client.Queryable<GroupPurchase>().OrderBy("Year").ToList();
                return result;
            }
            else if (param == "1")
            {
                sql = @"select *  from GroupPurchase n 
            where n.BeginDate <= Convert(varchar(10), getDate(), 120)
            and n.EndDate > Convert(varchar(10), getDate(), 120) order by n.Year ";
                var result = _client.SqlQueryable<GroupPurchase>(sql).ToList();
                return result;
            }
            else
            {
                sql = @"select *  from GroupPurchase n 
            where n.EndDate<= Convert(varchar(10),getDate(),120) 
            or n.BeginDate>Convert(varchar(10),getDate(),120) order by n.Year ";
                var result = _client.SqlQueryable<GroupPurchase>(sql).ToList();
                return result;
            }

            
         
        }
        //获取中选药品基本信息
        public List<PhaBaseInfo> GetChoseDrugInfo(string gpcode)
        {
            var result = _client.SqlQueryable<PhaBaseInfo>(@"select * from PhaBaseInfo o 
where --o.ValidFlag='1' 
 o.DrugCode in (select n.DrugCode from GroupDrugInfo n where n.GpCode=@gpcode)
").AddParameters(new { gpcode = gpcode, }).ToList();

            return result;
        }
        //获取非中选药品基本信息
        public List<PhaBaseInfo> GetunChoseDrugInfo(string gpcode, string drugcode)
        {
            var result = _client.SqlQueryable<PhaBaseInfo>(@"select * from PhaBaseInfo o 
where o.ValidFlag='1' 
and o.DrugCode not in (select n.DrugCode from GroupDrugInfo n where n.GpCode=@gpcode and  n.DrugCode=@drugcode
union all
select n.UnChosedrugCode from GroupChoseDrugCompare n where n.GpCode=@gpcode and  n.ChosedrugCode=@drugcode
)
").AddParameters(new { gpcode = gpcode, drugcode = drugcode }).ToList();

            return result;
        }

        //获取同品种药品基本信息
        public List<PhaBaseInfo> GetVarietyDrugInfo(string gpcode, string drugcode)
        {
            var result = _client.SqlQueryable<PhaBaseInfo>(@"select * from PhaBaseInfo o 
where o.ValidFlag='1' 
and o.DrugCode not in (select n.DrugCode from GroupDrugInfo n where n.GpCode=@gpcode and  n.DrugCode=@drugcode
union all
select n.VarietyDrugCode from GroupVarietyDrugCompare n where n.GpCode=@gpcode and  n.ChosedrugCode=@drugcode
)
").AddParameters(new { gpcode = gpcode, drugcode = drugcode }).ToList();

            return result;
        }
        public List<GroupChoseDrug> GetPageList(string gpCode, string drugCode, string isSet, PageModel page)
        {
            int count = 0;
            if (isSet == "" || isSet == null)//全部
            {
                var result = _client.SqlQueryable<GroupChoseDrug>(@" select distinct e.GpCode,s.Name GpName,s.Year,s.BeginDate,s.EndDate,e.DrugCode ChosedrugCode,
o.DrugName ChosedrugName,o.Specs,s.Enabled,o.ProducerName
  from GroupDrugInfo  e
  left join GroupPurchase s on s.Code=e.GpCode
  left join GroupChoseDrugCompare r on r.GpCode=e.GpCode and r.ChosedrugCode=e.DrugCode
  left join GroupVarietyDrugCompare a on a.GpCode=e.GpCode and a.ChoseDrugCode=e.DrugCode
  left join PhaBaseInfo o on e.DrugCode=o.DrugCode
 where e.GpCode=@gpCode and (e.DrugCode=@drugCode or ''=@drugCode)
").AddParameters(new { gpCode = gpCode, drugCode = drugCode, })
     .ToPageList(page.PageIndex, page.PageSize, ref count);
                page.TotalCount = count;
                return result;
            }
            else if (isSet == "1")//已维护数据
            {
                var result = _client.SqlQueryable<GroupChoseDrug>(@" select distinct e.GpCode,s.Name GpName,s.Year,s.BeginDate,s.EndDate,e.DrugCode ChosedrugCode,
o.DrugName ChosedrugName,o.Specs,s.Enabled,o.ProducerName
  from GroupDrugInfo  e
  left join GroupPurchase s on s.Code=e.GpCode
  left join GroupChoseDrugCompare r on r.GpCode=e.GpCode and r.ChosedrugCode=e.DrugCode
  left join GroupVarietyDrugCompare a on a.GpCode=e.GpCode and a.ChoseDrugCode=e.DrugCode
  left join PhaBaseInfo o on e.DrugCode=o.DrugCode
 where e.GpCode=@gpCode and (e.DrugCode=@drugCode or ''=@drugCode)
and (r.ChosedrugCode is not  null or a.ChoseDrugCode is not null)
").AddParameters(new { gpCode = gpCode, drugCode = drugCode, })
    .ToPageList(page.PageIndex, page.PageSize, ref count);
                page.TotalCount = count;
                return result;
            }
            else//未维护数据
            {
                var result = _client.SqlQueryable<GroupChoseDrug>(@" select distinct e.GpCode,s.Name GpName,s.Year,s.BeginDate,s.EndDate,e.DrugCode ChosedrugCode,
o.DrugName ChosedrugName,o.Specs,s.Enabled,o.ProducerName
  from GroupDrugInfo  e
  left join GroupPurchase s on s.Code=e.GpCode
  left join GroupChoseDrugCompare r on r.GpCode=e.GpCode and r.ChosedrugCode=e.DrugCode
  left join GroupVarietyDrugCompare a on a.GpCode=e.GpCode and a.ChoseDrugCode=e.DrugCode
  left join PhaBaseInfo o on e.DrugCode=o.DrugCode
 where e.GpCode=@gpCode and (e.DrugCode=@drugCode or ''=@drugCode)
and r.ChosedrugCode is   null and  a.ChoseDrugCode is  null
").AddParameters(new { gpCode = gpCode, drugCode = drugCode, })
   .ToPageList(page.PageIndex, page.PageSize, ref count);
                page.TotalCount = count;
                return result;
            }

        }
        //保存非中选药品维护信息
        public int SaveUnchoseDrug(List<GroupChoseDrugCompareNew> items, string gpCode, string drugCode)
        {

            var oldItems = _client.Queryable<GroupChoseDrugCompare>().Where(x => x.GpCode == gpCode && x.ChosedrugCode == drugCode).ToList();

            _client.BeginTran();
            try
            {
                #region 操作日志

                string oldCon = "";
                string newCon = "";
                oldItems.ForEach(x => oldCon += $"{x.GpCode},{drugCode},{x.UnChosedrugCode}{x.Ratio.ToString("0.######")}^");
                if (items != null)
                {
                    items.ForEach(x => newCon += $"{x.GpCode},{drugCode},{x.UnChosedrugCode}{x.Ratio.ToString("0.######")}^");
                }
                SysOperLogService.WriteSysOperLog(new SysOperLog
                {
                    EventType = "修改中选与非中选药品对照信息",
                    EventCon = "修改",
                    OldCon = oldCon.TrimEnd('^'),
                    NewCon = newCon.TrimEnd('^')
                });

                #endregion
                //删除信息
                // List<string> delItems = new List<string>();
                _client.Deleteable<GroupChoseDrugCompare>().Where(x => x.GpCode == gpCode && x.ChosedrugCode == drugCode).ExecuteCommand();

                //保存信息
                var insertItems = new List<GroupChoseDrugCompare>();
                var updateItems = new List<GroupChoseDrugCompare>();
                if (items != null)
                {
                    foreach (var item in items)
                    {
                        GroupChoseDrugCompare rr = new GroupChoseDrugCompare();
                        rr.GpCode = gpCode;
                        rr.ChosedrugCode = drugCode;
                        rr.UnChosedrugCode = item.UnChosedrugCode;
                        rr.Ratio = item.Ratio;
                        insertItems.Add(rr);
                    }
                    _client.Insertable(insertItems).ExecuteCommand();
                }
                // _client.Updateable(updateItems).WhereColumns(x => new { x.GpCode, x.DrugCode }).ExecuteCommand();

                _client.CommitTran();
            }
            catch
            {
                _client.RollbackTran();
                throw;
            }
            return 1;
        }
        //保存同品种药品信息
        public int SaveVarietyDrug(List<GroupVarietyDrugCompareNew> items, string gpCode, string drugCode)
        {

            var oldItems = _client.Queryable<GroupVarietyDrugCompare>().Where(x => x.GpCode == gpCode && x.ChoseDrugCode == drugCode).ToList();

            _client.BeginTran();
            try
            {
                #region 操作日志

                string oldCon = "";
                string newCon = "";
                oldItems.ForEach(x => oldCon += $"{x.GpCode},{drugCode},{x.VarietyDrugCode}{x.Ratio.ToString("0.######")}^");
                if (items != null)
                {
                    items.ForEach(x => newCon += $"{x.GpCode},{drugCode},{x.VarietyDrugCode}{x.Ratio.ToString("0.######")}^");
                }
                SysOperLogService.WriteSysOperLog(new SysOperLog
                {
                    EventType = "修改中选与同品种药品对照信息",
                    EventCon = "修改",
                    OldCon = oldCon.TrimEnd('^'),
                    NewCon = newCon.TrimEnd('^')
                });

                #endregion
                //删除信息
                // List<string> delItems = new List<string>();
                _client.Deleteable<GroupVarietyDrugCompare>().Where(x => x.GpCode == gpCode && x.ChoseDrugCode == drugCode).ExecuteCommand();

                //保存信息
                var insertItems = new List<GroupVarietyDrugCompare>();
                var updateItems = new List<GroupVarietyDrugCompare>();
                if (items != null)
                {
                    foreach (var item in items)
                    {
                        GroupVarietyDrugCompare rr = new GroupVarietyDrugCompare();
                        rr.GpCode = gpCode;
                        rr.ChoseDrugCode = drugCode;
                        rr.VarietyDrugCode = item.VarietyDrugCode;
                        rr.Ratio = item.Ratio;
                        insertItems.Add(rr);
                    }
                    _client.Insertable(insertItems).ExecuteCommand();
                }
                // _client.Updateable(updateItems).WhereColumns(x => new { x.GpCode, x.DrugCode }).ExecuteCommand();

                _client.CommitTran();
            }
            catch
            {
                _client.RollbackTran();
                throw;
            }
            return 1;
        }
        //新增或更新非中选药品对照信息
        public int Save(GroupChoseDrugCompare item)
        {
            if (string.IsNullOrEmpty(item.GpCode) && string.IsNullOrEmpty(item.ChosedrugCode) && string.IsNullOrEmpty(item.UnChosedrugCode))
                return 0;
            if (_client.Queryable<GroupChoseDrugCompare>().Where(t => t.GpCode == item.GpCode && t.ChosedrugCode == item.ChosedrugCode && t.UnChosedrugCode == item.UnChosedrugCode).Count() > 0)
            {
                return _client.Updateable<GroupChoseDrugCompare>(item).Where(t => t.GpCode == item.GpCode && t.ChosedrugCode == item.ChosedrugCode && t.UnChosedrugCode == item.UnChosedrugCode).ExecuteCommand();
            }
            else
            {
                return _client.Insertable<GroupChoseDrugCompare>(item).ExecuteCommand();
            }
        }
        //删除非中选药品对照信息
        public int Del(string gpCode, string chosedrugCode, string unChosedrugCode)
        {
            if (string.IsNullOrEmpty(gpCode) && string.IsNullOrEmpty(chosedrugCode) && string.IsNullOrEmpty(unChosedrugCode))
                return 0;
            // _client.BeginTran();
            try
            {
                _client.Deleteable<GroupChoseDrugCompare>().Where(t => t.GpCode == gpCode && t.ChosedrugCode == chosedrugCode && t.UnChosedrugCode == unChosedrugCode).ExecuteCommand();
                // _client.Deleteable<DeptCompare>().Where(t => t.MergeDeptCode == deptCode).ExecuteCommand();
                // _client.CommitTran();
            }
            catch (Exception e)
            {
                // _client.RollbackTran();
                return 0;
            }
            return 1;
        }

        //批量保存非中选对照信息
        public int SaveGroupUnchoseDrug(List<GroupChoseDrugCompare> items, string gpCode)
        {
            if (items == null || items.Count==0)
            {
                return -1;
            }
            _client.BeginTran();
            try
            {
                //删除信息
                // List<string> delItems = new List<string>();
                _client.Deleteable<GroupChoseDrugCompare>().Where(x => x.GpCode == gpCode ).ExecuteCommand();

                //保存信息
                var insertItems = new List<GroupChoseDrugCompare>();
                var updateItems = new List<GroupChoseDrugCompare>();
                if (items != null)
                {
                    foreach (var item in items)
                    {
                        GroupChoseDrugCompare rr = new GroupChoseDrugCompare();
                        rr.GpCode = gpCode;
                        rr.ChosedrugCode = item.ChosedrugCode;
                        rr.UnChosedrugCode = item.UnChosedrugCode;
                        rr.Ratio = item.Ratio;
                        insertItems.Add(rr);
                    }
                    _client.Insertable(insertItems).ExecuteCommand();
                }
                // _client.Updateable(updateItems).WhereColumns(x => new { x.GpCode, x.DrugCode }).ExecuteCommand();

                _client.CommitTran();
            }
            catch
            {
                _client.RollbackTran();
                throw;
            }
            return 1;
        }

        //批量保存同品种对照信息
        public int SaveGroupVarietyDrug(List<GroupVarietyDrugCompare> items, string gpCode)
        {
            if (items == null)
            {
                return -1;
            }
            _client.BeginTran();
            try
            {
                //删除信息
                // List<string> delItems = new List<string>();
                _client.Deleteable<GroupVarietyDrugCompare>().Where(x => x.GpCode == gpCode).ExecuteCommand();

                //保存信息
                var insertItems = new List<GroupVarietyDrugCompare>();
                var updateItems = new List<GroupVarietyDrugCompare>();
                if (items != null)
                {
                    foreach (var item in items)
                    {
                        GroupVarietyDrugCompare rr = new GroupVarietyDrugCompare();
                        rr.GpCode = gpCode;
                        rr.ChoseDrugCode = item.ChoseDrugCode;
                        rr.VarietyDrugCode = item.VarietyDrugCode;
                        rr.Ratio = item.Ratio;
                        insertItems.Add(rr);
                    }
                    _client.Insertable(insertItems).ExecuteCommand();
                }
                // _client.Updateable(updateItems).WhereColumns(x => new { x.GpCode, x.DrugCode }).ExecuteCommand();

                _client.CommitTran();
            }
            catch
            {
                _client.RollbackTran();
                throw;
            }
            return 1;
        }
        public int ImportUnchoseDrug(DataTable dt, string gpCode)
        {
            MessageException.CheckException(!dt.Columns.Contains("中选药品编码"), "导入文件不包含【中选药品编码】列!");
            MessageException.CheckException(!dt.Columns.Contains("非中选药品编码"), "导入文件不包含【非中选药品编码】列!");
            MessageException.CheckException(!dt.Columns.Contains("转换系数"), "导入文件不包含【转换系数】列!");
            var items = new List<GroupChoseDrugCompare>();
            foreach (DataRow row in dt.Rows)
            {
                items.Add(new GroupChoseDrugCompare
                {
                    GpCode = gpCode,
                    ChosedrugCode = row["中选药品编码"].AsString(),
                    UnChosedrugCode = row["非中选药品编码"].AsString(),
                    Ratio = row["转换系数"].AsDecimal(0),
                });
            }
            return SaveGroupUnchoseDrug(items, gpCode);
        }
        public int ImportVarietyDrug(DataTable dt, string gpCode)
        {
            MessageException.CheckException(!dt.Columns.Contains("中选药品编码"), "导入文件不包含【中选药品编码】列!");
            MessageException.CheckException(!dt.Columns.Contains("同品种药品编码"), "导入文件不包含【同品种药品编码】列!");
            MessageException.CheckException(!dt.Columns.Contains("转换系数"), "导入文件不包含【转换系数】列!");
            var items = new List<GroupVarietyDrugCompare>();
            foreach (DataRow row in dt.Rows)
            {
                items.Add(new GroupVarietyDrugCompare
                {
                    GpCode = gpCode,
                    ChoseDrugCode = row["中选药品编码"].AsString(),
                    VarietyDrugCode = row["同品种药品编码"].AsString(),
                    Ratio = row["转换系数"].AsDecimal(0),
                });
            }
            return SaveGroupVarietyDrug(items, gpCode);
        }
        public DataTable ImportTempletFzx()
        {
            var data = _client.Queryable<PhaBaseInfo>()
                   .Where(pb => pb.ValidFlag == "")
                   .Select(pb => (dynamic)new { 中选药品编码 = pb.DrugCode, 中选药品名称 = pb.DrugName, 中选药品规格 = pb.Specs, 非中选药品编码 = pb.DrugCode, 非中选药品名称 = pb.DrugName, 转换系数 = 1 })
                   .ToDataTable();

            return data;
        }
        public DataTable export(string gpcode)
        {
            var data = _client.Queryable<GroupDrugInfo>()
                .LeftJoin<GroupChoseDrugCompare>((g,r)=>g.DrugCode==r.ChosedrugCode && g.GpCode==r.GpCode)
                .LeftJoin<PhaBaseInfo>((g,r,p)=>g.DrugCode==p.DrugCode)
                .LeftJoin<PhaBaseInfo>((g,r,p,b)=>r.UnChosedrugCode==b.DrugCode)
                .Where(g=>g.GpCode==gpcode)
                .Select((g,r,p,b) => (dynamic)new
                {
                    中选药品编码 = g.DrugCode,
                    中选药品名称 = p.DrugName,
                    中选药品规格 = p.Specs,
                    中选药品生产厂家=p.ProducerName,
                    任务量=g.TaskQTY,
                    完成比例=g.TaskRatio,
                    非中选药品编码=r.UnChosedrugCode,
                    非中选药品名称=b.DrugName,
                    非中选规格=b.Specs,
                    非中选药品生产厂家 = b.ProducerName,
                    转换系数 =r.Ratio

                })
                .ToDataTable();

            return data;
        }
        public DataTable ImportTempletTpz()
        {
            var data = _client.Queryable<PhaBaseInfo>()
                   .Where(pb => pb.ValidFlag == "")
                   .Select(pb => (dynamic)new { 中选药品编码 = pb.DrugCode, 中选药品名称 = pb.DrugName, 中选药品规格 = pb.Specs, 同品种药品编码 = pb.DrugCode, 同品种药品名称 = pb.DrugName, 转换系数 = 1 })
                   .ToDataTable();

            return data;
        }
    }
}
